import pandas as pd

# Importing xlsx sheet 
file = '../original/RI-AUDIT-NET-RESULTS.xlsx'
df = pd.read_excel(file)

# Dropping first and last rows
df.drop(df.index[0], axis = 0, inplace=True)
df.drop(df.index[13], axis = 0, inplace=True)

# Add necessary columns
df["state"] = "RHODE ISLAND"
df["date"] = "2020-11-03"
df["office"] = "US PRESIDENT"

# Rename columns
df.rename(columns={"Batch Name": "county"}, inplace = True)
df.rename(columns={"Voting System Totals": "biden_votes"}, inplace = True)
df.rename(columns={"Unnamed: 2": "trump_votes"}, inplace = True)
df.rename(columns={"Unnamed: 3": "other_votes"}, inplace = True)
df.rename(columns={"Unnamed: 4": "ballots_cast_1"}, inplace = True)
df.rename(columns={"Audit Totals": "biden_audit"}, inplace = True)
df.rename(columns={"Unnamed: 6": "trump_audit"}, inplace = True)
df.rename(columns={"Unnamed: 7": "other_audit"}, inplace = True)
df.rename(columns={"Unnamed: 8": "ballots_cast_2"}, inplace = True)
df.rename(columns={"Unnamed: 9": "difference"}, inplace = True)

# Make all county names upper case
df["county"] = df["county"].str.upper()

df = df[['state', 'date', 'office', 'county', 'biden_votes', 'biden_audit', 'trump_votes', 'trump_audit', 'other_votes', 'other_audit', 'difference']]

# Fixing candidates
df["BIDEN"] = list(zip(df["biden_votes"], df["biden_audit"]))
df["TRUMP"] = list(zip(df["trump_votes"], df["trump_audit"]))
df["OTHER"] = list(zip(df["other_votes"], df["other_audit"]))

df = df.melt(["state", "date", "office", "county", "difference"],["BIDEN", "TRUMP", "OTHER"], var_name = "candidate")

df[["original_votes", "audited_votes"]] = pd.DataFrame(df["value"].tolist(), index=df.index)
df.drop("value", axis=1, inplace = True)

# Order
df = df[['state', 'date', 'office', 'county', 'candidate','original_votes', 'audited_votes', 'difference']]

# Save the work
df.to_csv('../ready/RI-cleaned.csv', index=False)
